/**************************************************************************
	Replication do-file: "Corruption in Customs"
	Cyril Chalendard, Ana Fernandes, Gael Raballand and Bob Rijkers
	
	Created on: 01/07/2022
**************************************************************************/

clear all
set more off, perm
cap log close
cls

* ----------------------- DIRECTORIES AND FOLDERS ----------------------- *

* Directories in which data are stored
global main "PUT YOUR DIRECTORY PATH HERE"
cd "$folder"

* Folders
global outputdata = "$main\Output Data"
cap mkdir "$main\Tables"
global tables = "$main\Tables"

* ----------------------------- BEGINS HERE ----------------------------- *

* -------
* Dataset
* -------
use "$outputdata\CFRR.dta", clear // open dataset

* Sample
keep if sample_int==1 & eis_f!=. // keep observations of interest

* Dataset
** Collapse by inspector-semester pairs
collapse (mean) sig_c_f overall_satisfaction pay_satisfaction esprit_de_corps discretion training information_risky_firms corruption_brokers corruption_colleagues corruption_supervisor ethics threats promotion_fairness if sample_reg==1 & nc_sem<11 & eis_f!=. , by(is insp_f nc_sem) // collapse keeping means by inspector-semester
** Collapse by inspectors
collapse (mean) sig_c_f overall_satisfaction pay_satisfaction esprit_de_corps discretion training information_risky_firms corruption_brokers corruption_colleagues corruption_supervisor ethics threats promotion_fairness , by(insp_f) // collapse keeping means by inspector

* Label variables
label var sig_c_f "Average significant excess interaction"


* ---------
* Table A26
* ---------

* Globals
global dep1 "overall_satisfaction pay_satisfaction esprit_de_corps discretion training information_risky_firms"
global dep2 "corruption_brokers corruption_colleagues corruption_supervisor ethics threats promotion_fairness"

* Matrix
forvalues x = 1/2 {
	mat tableA26_`x' = J(4,6,.)
	mat tableA26_`x'T = J(1,6,.)
}

* Loop for estimations
** First part
local i = 1
local j = 1
foreach x in $dep1 {

	qui: reg `x' sig_c_f

	mat tableA26_1[`i',`j'] = _b[sig_c_f]
	local i = `i' + 1
	mat tableA26_1[`i',`j'] = _se[sig_c_f]
	mat tableA26_1T[1,`j'] = e(N)
	local i = `i' + 2
	mat tableA26_1[`i',`j'] = e(r2)
	local i = `i' -3
	local j = `j' +1
}
** Second part
local i = 1
local j = 1
foreach x in $dep2 {

	qui: reg `x' sig_c_f

	mat tableA26_2[`i',`j'] = _b[sig_c_f]
	local i = `i' + 1
	mat tableA26_2[`i',`j'] = _se[sig_c_f]
	mat tableA26_2T[1,`j'] = e(N)
	local i = `i' + 2
	mat tableA26_2[`i',`j'] = e(r2)
	local i = `i' -3
	local j = `j' +1
}


* Export
* ------
putexcel set "$tables\Table A26.xlsx", replace sheet(TableA26) // create a new excel spreadsheet

* Dependent variables
putexcel A1 = "Dependent variable:", left vcenter bold
local cells `" "B" "C" "D" "E" "F" "G" "B" "C" "D" "E" "F" "G" "'
local labels `" "Overall job satisfaction" "Pay satisfaction" "Esprit de corps" "Sufficient discretion" "Sufficient training" "Knowledge about risky firms" "Corruption brokers" "Corruption colleagues" "Corruption supervisors" "Ethical behavior is sanctioned" "Receives threats" "Promotions are fair" "'
forvalues i = 1(1)12 {

	local x: word `i' of `labels'
	local y: word `i' of `cells'
	
	if `i' < 7 {
		putexcel `y'1 = "`x'", hcenter vcenter bold	
	}
	
	else {
		putexcel `y'8 = "`x'", hcenter vcenter bold	
	}
	
}

* Coefficients
local j = 1
forvalues i = 3(7)10 {

	putexcel A`i' = "Average significant excess interaction", left vcenter
	putexcel B`i' = matrix(tableA26_`j'), nformat(0.000) hcenter vcenter
	local j = `j'+1
}
** Observations
local j = 1
forvalues i = 5(7)12 {

	putexcel A`i' = "Observations", left vcenter
	putexcel B`i' = matrix(tableA26_`j'T), nformat(number_sep) hcenter vcenter
	local j = `j'+1
}
** R2
forvalues i = 6(7)13 {
	putexcel A`i' = "R-squared", left vcenter
}

* Column numbers
global cells "B C D E F G B C D E F G"
forvalues i = 1(1)12 {

	local y: word `i' of $cells

	if `i' < 7 {
		putexcel `y'2 = "(`i')", hcenter vcenter
	}

	if `i' > 6 {
		putexcel `y'9 = "(`i')", hcenter vcenter
	}
}
macro drop cells


* -------------------------------- ENDS HERE -------------------------------- *